﻿using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Net.Mail;
//using jmail;
using System.Reflection;

/// <summary>
/// SqlHelper 的摘要说明
/// </summary>
public class SqlHelper
{
    static private readonly string CONN_STRING;
    private const string strSpecifyChars = ".-_~"; //定义用户名部分可以输入的一些特殊字符: “ .” “ -” “ _” “ ~” 符号

    static SqlHelper()
    {

        CONN_STRING = System.Configuration.ConfigurationManager.AppSettings["constr"].ToString();

    }

    /// <summary>
    /// 防止sql注入替换单引号
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    static public string ToSqlValueString(string input)
    {
        if (input == null) return "";

        return input.Replace("'", "''");
    }
    static public string ToChgHTMLString(string input)
    {
        if (input == null) return "";
        input = input.Replace("<", "&lt;");
        input = input.Replace(">", "&gt;");
        input = input.Replace("\r\n", "<br>");
        return input;
    }
    
    /// <summary>
    /// HTML转换
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    static public string ToChgHTMLString2(string input)
    {
        if (input == null) return "";
        input = input.Replace("&lt;", "<");
        input = input.Replace("&gt;", ">");
        input = input.Replace("&amp;", "&");
        input = input.Replace("&quot;", "\"");
        input = input.Replace("&copy;", "©");
        input = input.Replace("&reg;", "®");
        input = input.Replace("&yen;", "￥");
        input = input.Replace("&nbsp;", " ");
        input = input.Replace("\r\n", "<br>");
        return input;
    }

    static public string changeDoubleFlag(string strTemp)
    {
        strTemp = strTemp + "";
        strTemp = strTemp.Replace("：", ":");
        strTemp = strTemp.Replace("∶", ":");
        return strTemp;
    }
    public static bool IsInteger(string str)
    {
        str = str + "";
        if (str == null || str.Length == 0)
        {
            return false;
        }
        System.Text.ASCIIEncoding ascii = new System.Text.ASCIIEncoding();

        byte[] bytestr = ascii.GetBytes(str);

        foreach (byte c in bytestr)
        {
            if (c < 48 || c > 57)
            {
                return false;
            }
        }
        return true;
    }
    public static int ExecuteSQL(string SQLStr)
    {

        int num;
        SqlConnection conn = new SqlConnection(CONN_STRING);
        conn.Open();
        SqlCommand comm = new SqlCommand(SQLStr, conn);

        try
        { 
            num = Convert.ToInt32(comm.ExecuteScalar());
            conn.Close();
            return num;
        }
        catch
        {
            conn.Close();
            return 0;
        }


    }


    /// <summary>
    /// 传入一个sql语句，和一组可选参数执行
    /// </summary>
    /// <param name="sql">要执行的sql语句</param>
    /// <param name="parameters"></param>
    /// 
    static public void Execute(string sql, params SqlParameter[] parameters)
    {
        using (SqlConnection conn = new SqlConnection(CONN_STRING))
        {
            conn.Open();
            using (SqlCommand comm = new SqlCommand(sql, conn))
            {
                if (parameters != null && parameters.Length > 0)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        comm.Parameters.Add(para);
                    }
                }
                comm.ExecuteNonQuery();
                conn.Close();
            }
        }
    }

    static public int IsConnectionSuccess()
    {
        string connStr = System.Configuration.ConfigurationManager.AppSettings["constr"].ToString();// "data source=219.234.95.88;uid=sa;pwd=mm_sqlserverR01;database=MM_Webinar;Connect Timeout=15";
        SqlConnection conn = new SqlConnection(connStr);

        try
        {
            conn.Open();

        }
        catch
        {
            return 0;

        }
        conn.Close();

        return 1;

    }

    public static bool GetBoolValue(string SQLStr, string TableName)
    {
        DataSet ds;
        using (SqlConnection conn = new SqlConnection(CONN_STRING))
        {
            conn.Open();
            try
            {
                ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter(SQLStr, conn);
                adapter.Fill(ds, TableName);
                return true;
            }
            catch
            {
                ds = null;
                return false;
            }
        }
    }


    /// <summary>
    /// 执行一个sql语句，并返回一个整形数
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="parameters"></param>
    /// <returns></returns>
    static public int GetScaler(string sql, params SqlParameter[] parameters)
    {
        using (SqlConnection conn = new SqlConnection(CONN_STRING))
        {
            conn.Open();
            using (SqlCommand comm = new SqlCommand(sql, conn))
            {
                if (parameters != null && parameters.Length > 0)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        comm.Parameters.Add(para);
                    }
                }
                object obj = comm.ExecuteScalar();
                int result = Convert.ToInt32(obj);
                return result;
            }
        }
    }

    /// <summary>
    /// 执行一个sql语句，并返回一个OBJECT
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="parameters"></param>
    /// <returns></returns>
    static public object GetObjScaler(string sql, params SqlParameter[] parameters)
    {
        using (SqlConnection conn = new SqlConnection(CONN_STRING))
        {
            conn.Open();
            using (SqlCommand comm = new SqlCommand(sql, conn))
            {
                if (parameters != null && parameters.Length > 0)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        comm.Parameters.Add(para);
                    }
                }
                object obj = comm.ExecuteScalar();
                return obj;
            }
        }
    }



    /// <summary>
    /// 执行一个sql语句返回一个DataReader
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="parameters"></param>
    /// <returns></returns>
    static public SqlDataReader GetReader(string sql, params SqlParameter[] parameters)
    {
        SqlConnection conn = new SqlConnection(CONN_STRING);
        conn.Open();
        using (SqlCommand comm = new SqlCommand(sql, conn))
        {
            if (parameters != null && parameters.Length > 0)
            {
                foreach (SqlParameter para in parameters)
                {
                    comm.Parameters.Add(para);
                }
            }
            return comm.ExecuteReader();
        }
    }

    /// <summary>
    /// 执行sql语句返回sql语句执行所得的数据集。
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="parameters"></param>
    /// <returns></returns>
    static public DataSet GetDataSet(string sql, params SqlParameter[] parameters)
    {
        using (SqlConnection conn = new SqlConnection(CONN_STRING))
        {
            conn.Open();
            using (SqlCommand comm = new SqlCommand(sql, conn))
            {
                if (parameters != null && parameters.Length > 0)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        comm.Parameters.Add(para);
                    }
                }
                using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
                {
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    conn.Close();
                    return ds;
                }
            }
        }
    }
    /// <summary>
    /// 分页所得的数据集。
    /// </summary>
    public static void ExecuteSQL(string sql, string TableName, int start, int PageSize, out DataSet ds)
    {
        SqlConnection conn = new SqlConnection(CONN_STRING);
        conn.Open();
        SqlCommand comm = new SqlCommand(sql, conn);
        SqlDataAdapter adapter = new SqlDataAdapter(comm);
        ds = new DataSet();

        try
        {
            adapter.Fill(ds, start, PageSize, TableName);
            conn.Close();
        }
        catch
        {
            ds = null;
        }


    }

    public static void ExecuteMoreSql(string sql1, string sql2)
    {

        SqlConnection conn = new SqlConnection(CONN_STRING);
        System.Data.SqlClient.SqlTransaction transaction = null;
        conn.Open();
        SqlCommand comm1 = new SqlCommand(sql1, conn);
        SqlCommand comm2 = new SqlCommand(sql2, conn);


        transaction = conn.BeginTransaction();

        try
        {
            comm1.Transaction = transaction;
            comm2.Transaction = transaction;

            comm1.ExecuteNonQuery();
            comm2.ExecuteNonQuery();
            transaction.Commit();

        }
        catch
        {
            transaction.Rollback();

        }
        finally
        {
            conn.Close();

        }

        return;

    }


    /// <summary>
    /// 利用事务执行Sql语句
    /// </summary>
    public static void RunSqlTransaction(string[] sql_str)
    {

        SqlConnection myConnection = new SqlConnection(CONN_STRING);

        // 初始化
        myConnection.Open();
        SqlCommand myCommand = myConnection.CreateCommand();
        SqlTransaction myTrans;

        // 开启
        myTrans = myConnection.BeginTransaction();

        // Both Transaction object and transaction object
        myCommand.Connection = myConnection;
        myCommand.Transaction = myTrans;
        try
        {
            //myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";     
            //myCommand.ExecuteNonQuery();     
            //myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";    
            //myCommand.ExecuteNonQuery();
            for (int i = 0; i < sql_str.Length; i++)
            {
                myCommand.CommandText = sql_str[i];
                myCommand.ExecuteNonQuery();
            }

            myTrans.Commit();
            // 成功
        }
        catch (Exception ex)
        {
            ex.ToString();
            try
            {
                myTrans.Rollback();
            }

            catch (SqlException exx)
            {
                exx.ToString();
                //error
            }

        }
        finally
        {
            myConnection.Close();
        }
    }

    /// <summary>
    /// 执行sql语句返回sql语句执行所得的数据集。
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="parameters"></param>
    /// <returns></returns>
    static public DataSet GetDataSet(string sql, string tableName, params SqlParameter[] parameters)
    {
        using (SqlConnection conn = new SqlConnection(CONN_STRING))
        {
            conn.Open();
            using (SqlCommand comm = new SqlCommand(sql, conn))
            {
                if (parameters != null && parameters.Length > 0)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        comm.Parameters.Add(para);
                    }
                }
                using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
                {
                    DataSet ds = new DataSet();
                    adapter.Fill(ds, tableName);
                    conn.Close();
                    return ds;
                }
            }
        }
    }
  
}
